using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using OpenMVCRM.Entities;

namespace OpenMVCRM.DataAccess
{
    class ContactPersonDA
    {
        readonly static string connString = ConfigurationManager.ConnectionStrings["CRMContext"].ConnectionString;
        public static OperationStatus SaveContactPerson(ContactPerson person)
        {
            var opStatus = new OperationStatus();

            using (var conn = new SqlConnection(connString))
            {
                using (var cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "uspSaveContactPerson";
                    cmd.Parameters.AddWithValue("@id", person.ID);
                    cmd.Parameters.AddWithValue("@firstName", person.FirstName);
                    cmd.Parameters.AddWithValue("@lastName", (object)person.LastName ?? DBNull.Value);
                    cmd.Parameters.AddWithValue("@emailAddress", (object)person.EmailAddress ?? DBNull.Value);
                    cmd.Parameters.AddWithValue("@homePhone", (object)person.HomePhone ?? DBNull.Value);
                    cmd.Parameters.AddWithValue("@mobilePhone", (object)person.MobilePhone ?? DBNull.Value);
                    cmd.Parameters.AddWithValue("@skypeId", (object)person.SkypeID ?? DBNull.Value);
                    cmd.Parameters.AddWithValue("@organizationId", person.Organization.ID);
                    cmd.Parameters.AddWithValue("@ownerOrgId", person.Organization.ID);
                    cmd.Parameters.AddWithValue("@userId", person.ModifiedById);

                    DbParameter returnValue;
                    returnValue = cmd.CreateParameter();
                    returnValue.Direction = ParameterDirection.ReturnValue;
                    cmd.Parameters.Add(returnValue);

                    conn.Open();
                    cmd.ExecuteNonQuery();
                    int result = Convert.ToInt32(returnValue.Value);
                    conn.Close();
                    opStatus.OperationID = result;
                    opStatus.Status = true;
                }
            }

            return opStatus;
        }

        public static IList<ContactPerson> GetContactPersons(int ownerOrganizationId)
        {
            List<ContactPerson> clientList = new List<ContactPerson>();
            using (var conn = new SqlConnection(connString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "uspGetContactPersons";
                    cmd.Parameters.AddWithValue("@ownerOrganizationId", ownerOrganizationId);
                    using (var reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            int idLocation = reader.GetOrdinal("PersonID");
                            int fnameLocation = reader.GetOrdinal("FirstName");
                            int lnameLocation = reader.GetOrdinal("LastName");
                            int emailLocation = reader.GetOrdinal("EmailAddress");
                            int homePhoneLocation = reader.GetOrdinal("HomePhone");
                            int mobilePhoneLocation = reader.GetOrdinal("MobilePhone");
                            int skypeIdLocation = reader.GetOrdinal("SkypeId");
                            int parentOrgIdIdLocation = reader.GetOrdinal("ParentOrganizationID");
                            int parentOrgNameIdLocation = reader.GetOrdinal("ParentOrganizationName");
                            int dateCreatedLocation = reader.GetOrdinal("DateCreated");


                            while (reader.Read())
                            {
                                var person = new ContactPerson();
                                person.ID = reader.GetInt32(idLocation);
                                if (!reader.IsDBNull(fnameLocation))
                                    person.FirstName = reader.GetString(fnameLocation);
                                if (!reader.IsDBNull(lnameLocation))
                                    person.LastName = reader.GetString(lnameLocation);
                                if (!reader.IsDBNull(emailLocation))
                                    person.EmailAddress = reader.GetString(emailLocation);
                                if (!reader.IsDBNull(homePhoneLocation))
                                    person.HomePhone = reader.GetString(homePhoneLocation);
                                if (!reader.IsDBNull(mobilePhoneLocation))
                                    person.MobilePhone = reader.GetString(mobilePhoneLocation);

                                if (!reader.IsDBNull(skypeIdLocation))
                                    person.SkypeID = reader.GetString(skypeIdLocation);

                                if (!reader.IsDBNull(parentOrgNameIdLocation))
                                    person.Organization.Name = reader.GetString(parentOrgNameIdLocation);


                                clientList.Add(person);
                            }
                        }
                    }
                }

            }
            return clientList;
        }

    }
}
